import datetime
import json
import os

import cx_Oracle
import oracle_procedure

"""在存储中匹配字符串"""

user = "pigpos"
passwd = "P1g#2023pos"
listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
# listener = '10.240.24.135/NMDCFARM'
# listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()
table_list = []


def select(sql) -> json:
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


def write2txt(cur_procedure, rs):
    # print('写文件')
    path = "C:/Users/asyf/Desktop/procedure"
    if not os.path.exists(path):
        os.mkdir(path)
    file = path + "/" + cur_procedure + ".txt"
    with open(file, mode='w', encoding='utf-8') as f:
        j = json.loads(rs)
        for text in j:
            # print(text['TEXT'])
            f.write(text['TEXT'])


def find(key, array, path):
    # 查询当前存储是否包括
    cur_procedure = array[0]['NAME']
    path = path + "->" + cur_procedure
    sql = " SELECT text FROM user_source WHERE NAME = '" + cur_procedure + "' ORDER BY line"
    # 查询依赖存储是否包括
    rs = select(sql)
    # 将存储写到文件
    write2txt(cur_procedure, rs)
    # print(type(rs))
    a = 0
    j = json.loads(rs)
    content = ''
    # 整理存储的字符
    for text in j:
        content = content + text['TEXT']
    content = content.replace('\n', '')
    content = content.replace('\t', '')
    content = content.replace(' ', '')
    if key.upper() in content.upper():
        a = 1
        pass
    if a == 1:
        print('存储:%s,是否包含:%d,路径:%s' % (cur_procedure, a, path))
        if cur_procedure not in table_list:
            table_list.append(cur_procedure)
    for t in array:
        ref_name = t['REFERENCED_NAME']
        type_ = t['REFERENCED_TYPE']
        if type_ == 'PROCEDURE':
            dep = t['dependencies']
            find(key, dep, path)


# 消除空白字符串后再去查询更精确
if __name__ == '__main__':
    # 查询报错中文描述
    """ SELECT  *   FROM MAS_ERROR_MESSAGE where  error_id = 'FRCHK074'"""
    print('开始查询')
    # t = 'updateFR_MS_ACC_TRN_MEDICINE'
    t = 'FR_SW_JOB_COST'
    # t = 'FR_SP_SW_GEN_REPORT_CORP_ALL'
    # t = 'FR_SP_SW_GEN_RPT_EFF_PORCITEC'
    # t = 'FR_SW_JOB_COSTING_GEN_GL_SAP'
    jsonFilePath = "C:/Users/asyf/Desktop/" + t + ".json"

    with open(jsonFilePath) as f:
        j = json.load(f)
    # print(j) 正则查找into
    key = "FR_SWC430_CAL_COS_AI_USE_TH"
    print('key:%s' % key)
    path = ''
    find(key, j, path)
    print('key:%s %s' % (key, '\n结果:'))
    for t in table_list:
        line_list = oracle_procedure.get_line_aray(key, t)
        print(t + ' - line=' + str(line_list))
